package com.acme.team02.chat.server;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.LinkedList;
import java.util.List;

import com.acme.team02.chat.server.domain.Message;

public class DatabaseConnectivity {
	private static String dbURL = "jdbc:derby://localhost:1527/team02ChatDB;user=app;password=app";

	private static Connection connection;

	/**
	 * open connection to database
	 */
	public void connect() {
		try {
			Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
			connection = DriverManager.getConnection(dbURL);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * Add message to database
	 * @param msg
	 * @throws SQLException 
	 */
	public void addMessage(Message msg) throws SQLException {
		String statementTemplate = "insert into history (Date, Room, Username, Text) values (?, ?, ?, ?)";

		PreparedStatement prepStatement = connection
				.prepareStatement(statementTemplate);

		prepStatement.setTimestamp(1, new Timestamp(msg.getDate().getTime()));
		prepStatement.setString(2, msg.getRoom());
		prepStatement.setString(3, msg.getSender());
		prepStatement.setString(4, msg.getText());
		
		prepStatement.executeUpdate();
	}

	/**
	 * Get messages from database
	 * @param room
	 * @return
	 */
	public List<Message> getHistory(String room) {
		
		String statementTemplate = "select * from history where Room = '"+room+"'";
		
		PreparedStatement prepStatement;
		try {
			prepStatement = connection.prepareStatement(statementTemplate);
			ResultSet result = prepStatement.executeQuery();
			
			LinkedList<Message> list = new LinkedList<>();
			
			while(result.next()){
				Message msg = new Message(new Date(result.getTimestamp(1).getTime()), result.getString(2), result.getString(3), result.getString(4));
				list.add(msg);
			}
			
			
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return new LinkedList<Message>();

	}

	/**
	 * Close connection to database
	 */
	public void close() {
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
